﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Collections;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using FileHelpers;
using FileHelpers.DataLink;
using System.Net;
using System.IO;
using FtpLib;
using System.Data.SqlClient;
using System.Security.Cryptography;
using XPTable.Models;
using XPTable;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Threading;


namespace CDRtoSQL
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        public class LogRecord
        {
            public DateTime call_start;
            public DateTime call_end;
            public string call_from;
            public string call_to;
            public string call_text;
            public int call_idnum;
        }

        public class CalculatedLogRecord
        {
            DateTime _call_start;
            TimeSpan _call_duration;
            string _call_from;
            string _call_to;
            string _call_text;

            public DateTime call_start
            {
                get { return _call_start; }
                set { _call_start = value; }
            }
            public TimeSpan call_duration
            {
                get { return _call_duration; }
                set { _call_duration = value; }
            }
            public string call_from
            {
                get { return _call_from; }
                set { _call_from = value; }
            }
            public string call_to
            {
                get { return _call_to; }
                set { _call_to = value; }
            }
            public string call_text
            {
                get { return _call_text; }
                set { _call_text = value; }
            }
        }

        static double ConvertToUnixTimestamp(DateTime date)
        {
            DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            TimeSpan diff = date - origin;
            return Math.Floor(diff.TotalSeconds);
        }

        static DateTime ConvertFromUnixTimestamp(double timestamp)
        {
            DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            return origin.AddSeconds(timestamp);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            columnModel1.Columns.Add(new TextColumn("call_start"));
            columnModel1.Columns.Add(new TextColumn("call_from"));
            columnModel1.Columns.Add(new TextColumn("call_to"));
            columnModel1.Columns.Add(new TextColumn("call_duration"));
            columnModel1.Columns.Add(new TextColumn("call_duration_round"));
            columnModel1.Columns.Add(new TextColumn("call_text"));
            dateTimePicker1.Value = DateTime.Today;
            dateTimePicker2.Value = DateTime.Today;
            table1.NoItemsText = "Выберите период времени.";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Form2 f = new Form2();
            f.Show();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            tableModel1.Rows.Clear();
            ArrayList readyTable = GetUniqueEntrysByDate(dateTimePicker1.Value, dateTimePicker2.Value);
            if (readyTable.Count == 0) table1.NoItemsText = "Нет данных за этот период.";
            else
            {
                foreach (CalculatedLogRecord rec in readyTable)
                {
                    if (rec.call_duration == TimeSpan.FromSeconds(0) && rec.call_text == "normal call clearing (16)") continue;
                    string[] localstring = new string[6];
                    localstring[0] = rec.call_start.ToString();
                    localstring[1] = rec.call_from;
                    localstring[2] = rec.call_to;
                    localstring[3] = rec.call_duration.ToString();
                    //округляем время до минут в большую сторону всегда
                    localstring[4] = (((int)rec.call_duration.TotalMinutes) + (rec.call_duration.TotalSeconds > 0 ? 1 : 0)).ToString();
                    localstring[5] = rec.call_text;
                    tableModel1.Rows.Add(new Row(localstring));
                }
            }
        }

        public ArrayList GetUniqueEntrysByDate(DateTime start_date, DateTime end_date)
        {
            ArrayList ResultTable = new ArrayList();
            string sQuery = "SELECT DISTINCT call_id FROM CDRStat WHERE call_start>\'" + ConvertToUnixTimestamp(start_date) + "\' AND call_end<\'" + ConvertToUnixTimestamp(end_date) + "\'";
            using (SqlConnection sqlConn = new SqlConnection("Server=ULH-VM-SQL4;Database=CDRStats;Trusted_Connection=True;"))
            {
                SqlCommand sqlQuery = new SqlCommand(sQuery, sqlConn);
                sqlConn.Open();
                SqlDataReader reader = sqlQuery.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        CalculatedLogRecord rec = CountAllStatByCallID(reader["call_id"].ToString());
                        if (rec != null)
                        {
                            ResultTable.Add(rec);
                        }
                    }
                }
                sqlConn.Close();
            }
            return ResultTable;
        }

        public CalculatedLogRecord CountAllStatByCallID(string call_id)
        {
            CalculatedLogRecord returnValue = new CalculatedLogRecord();
            string sQuery = "SELECT * FROM CDRStat WHERE call_id=\'" + call_id + "\'";
            int numRows = 0;

            ArrayList logData = new ArrayList();
            
            using (SqlConnection sqlConn = new SqlConnection("Server=" + Properties.Settings.Default.db_hostname + ";Database=" + Properties.Settings.Default.db_name + ";Trusted_Connection=True;"))
            {
                SqlCommand sqlQuery = new SqlCommand(sQuery, sqlConn);
                sqlConn.Open();
                SqlDataReader reader = sqlQuery.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        LogRecord logLine = new LogRecord();
                        logLine.call_from = reader["call_from"].ToString();
                        logLine.call_to = reader["call_to"].ToString();
                        logLine.call_end = ConvertFromUnixTimestamp((double)reader.GetInt64(1));
                        logLine.call_start = ConvertFromUnixTimestamp((double)reader.GetInt64(0));
                        logLine.call_text = reader["call_text"].ToString();
                        logLine.call_idnum = Convert.ToInt32(reader["call_idnum"]);
                        logData.Add(logLine);
                        numRows++;
                    }
                }
                sqlConn.Close();
            }

            if (numRows > 1)
            {
                LogRecord line1 = (LogRecord)logData[0];
                LogRecord line2 = (LogRecord)logData[1];
                if (line1.call_idnum < line2.call_idnum)
                {
                    returnValue.call_start = line1.call_start;
                    returnValue.call_from = line2.call_from;
                    returnValue.call_to = line2.call_to;
                    returnValue.call_duration = line1.call_end - line1.call_start;
                    returnValue.call_text = line1.call_text;
                }
                else
                {
                    returnValue.call_start = line2.call_start;
                    returnValue.call_from = line1.call_from;
                    returnValue.call_to = line1.call_to;
                    returnValue.call_duration = line2.call_end - line2.call_start;
                    returnValue.call_text = line2.call_text;
                }
                return returnValue;
            }
            return returnValue=null;

            //}
            //return returnValue = null;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            ExportToExcel();
        }

        public void ExportToExcel()
        {

            if (tableModel1.Rows.Count > 0)
            {
                Excel.Application oXL;
                Excel._Workbook oWB;
                Excel._Worksheet oSheet;

                try
                {
                    //Start Excel and get Application object.
                    oXL = new Excel.Application();
                    oXL.Visible = false;

                    //Get a new workbook.
                    oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                    oSheet = (Excel._Worksheet)oWB.ActiveSheet;

                    //Add table headers going cell by cell.
                    oSheet.Cells[1, 1] = "call start";
                    oSheet.Cells[1, 2] = "call from";
                    oSheet.Cells[1, 3] = "call to";
                    oSheet.Cells[1, 4] = "call duration";
                    oSheet.Cells[1, 5] = "call duration round";
                    oSheet.Cells[1, 6] = "call text";


                    // Autofilter and fix first row
                    oSheet.Application.ActiveWindow.SplitRow = 1;
                    oSheet.Application.ActiveWindow.FreezePanes = true;
                    Excel.Range firstRow = (Excel.Range)oSheet.Rows[1];
                    firstRow.Activate();
                    firstRow.Select();
                    firstRow.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);

                    //Set column size
                    oSheet.Columns["A", Type.Missing].ColumnWidth = 22;
                    oSheet.Columns["B", Type.Missing].ColumnWidth = 11;
                    oSheet.Columns["C", Type.Missing].ColumnWidth = 22;
                    oSheet.Columns["D", Type.Missing].ColumnWidth = 12;
                    oSheet.Columns["E", Type.Missing].ColumnWidth = 22;
                    oSheet.Columns["F", Type.Missing].ColumnWidth = 60;

                    //Format A1:F1 as bold, vertical alignment = center.
                    oSheet.get_Range("A1", "F1").Font.Bold = true;
                    oSheet.get_Range("A1", "F1").VerticalAlignment =
                        Excel.XlVAlign.xlVAlignCenter;

                    //fill data row by row
                    for (int r = 0; r < tableModel1.Rows.Count; r++)
                    {
                        for (int i = 0; i < columnModel1.Columns.Count; i++)
                        {
                            oSheet.Cells[r + 2, i + 1] = tableModel1.Rows[r].Cells[i].Text;
                        }

                    }

                    //show save dialog
                    saveFileDialog1.AddExtension = true;
                    saveFileDialog1.DefaultExt = ".xls";
                    saveFileDialog1.ShowDialog();

                    //save
                    oWB.Saved = true;
                    oWB.SaveCopyAs(saveFileDialog1.FileName);
                    oXL.Quit();
                }
                catch (Exception theException)
                {
                    String errorMessage;
                    errorMessage = "Error: ";
                    errorMessage = String.Concat(errorMessage, theException.Message);
                    errorMessage = String.Concat(errorMessage, " Line: ");
                    errorMessage = String.Concat(errorMessage, theException.Source);

                    MessageBox.Show(errorMessage, "Error");
                }
            }
            else MessageBox.Show("Нечего экспортировать");
        }
    }

}

